package org.anti996.bankms.dao;

import org.anti996.bankms.model.Account;
import org.anti996.bankms.model.Card;
import org.anti996.bankms.model.TRecord;
import org.anti996.bankms.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDao {
    private static final Connection conn = DBUtil.getConnection();

    //登录
    public static Account login(String username, String PASSWORD) {//获取账户信息
        String sql = "SELECT * FROM account WHERE username=? and passwd=? and account_close is null";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setString(1, username);
            Statement.setString(2, PASSWORD);
            ResultSet set = Statement.executeQuery();
            if (set.next()) {
                String us = set.getString("username");
                String pa = set.getString("passwd");
                String na = set.getString("name");
                String idnumber = set.getString("idnumber");
                String hometown = set.getString("hometown");
                String ph = set.getString("phone");
                Timestamp open = set.getTimestamp("account_open");
                Timestamp close = set.getTimestamp("account_open");
                return new Account(us, pa, na, idnumber, hometown, ph, open, close);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public static List<Card> getAllCards(Account account) {//获取用户全部卡的信息
        List<Card> cards = new ArrayList<>();
        String sql = "SELECT * FROM card WHERE username=? and card_close is null";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setString(1, account.getUsername());
            ResultSet set = Statement.executeQuery();
            while (set.next()) {
                int Id = set.getInt("id");
                String username = set.getString("username");
                double balance = set.getDouble("balance");
                String currency = set.getString("currency");
                Timestamp card_open = set.getTimestamp("card_open");
                Timestamp card_close = set.getTimestamp("card_close");
                Card card = new Card(Id, username, balance, currency, card_open, card_close);
                cards.add(card);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return cards;
    }

    public static List<Card> getAllCardsSortByUsername() {//获取用户全部卡的信息
        List<Card> cards = new ArrayList<>();
        String sql = "SELECT * FROM card WHERE card_close is null ORDER BY username";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            ResultSet set = Statement.executeQuery();
            while (set.next()) {
                int Id = set.getInt("id");
                String username = set.getString("username");
                double balance = set.getDouble("balance");
                String currency = set.getString("currency");
                Timestamp card_open = set.getTimestamp("card_open");
                Timestamp card_close = set.getTimestamp("card_close");
                Card card = new Card(Id, username, balance, currency, card_open, card_close);
                cards.add(card);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return cards;
    }


    public static Map<String, String> getCurrencyNames() {//币种和名称
        Map<String, String> map = new HashMap<>();
        String sql = "SELECT currency,name FROM exchange_rate";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            ResultSet set = Statement.executeQuery();
            while (set.next()) {
                String currency = set.getString("currency");
                String name = set.getString("name");
                map.put(currency, name);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return map;
    }

    public static Map<String, String> CURRENCY_NAMES = getCurrencyNames();

    public static Map<String, Double> getCurrencyRates() {//币种和名称
        Map<String, Double> map = new HashMap<>();
        String sql = "SELECT currency,rate FROM exchange_rate";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            ResultSet set = Statement.executeQuery();
            while (set.next()) {
                String currency = set.getString("currency");
                double rate = set.getDouble("rate");
                map.put(currency, rate);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return map;
    }

    public static Map<String, Double> CURRENCY_RATES = getCurrencyRates();


    public static Card getCardById(int id) {//获取card
        String sql = "SELECT * FROM card WHERE id=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setInt(1, id);
            ResultSet set = Statement.executeQuery();
            while (set.next()) {
                int Id = set.getInt("id");
                String username = set.getString("username");
                double balance = set.getDouble("balance");
                String currency = set.getString("currency");
                Timestamp card_open = set.getTimestamp("card_open");
                Timestamp card_close = set.getTimestamp("card_close");
                Card card = new Card(Id, username, balance, currency, card_open, card_close);
                return card;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public static void putMoneyOut1(Card card, double money) {//取钱基本操作
        String sql = "UPDATE card SET balance=balance-? where id=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setDouble(1, money);
            Statement.setInt(2, card.getId());
            int result = Statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static boolean putMoneyOut(Card card, double money) {//取钱+记录
        if (card.getBalance() >= money) {
            putMoneyOut1(card, money);
//            System.out.println("取钱成功");
            String sql = "INSERT INTO record (id_from,amount) VALUES (?,?)";
            try {
                // 2、获取预编译SQL语句的对象
                PreparedStatement Statement = conn.prepareStatement(sql);
                Statement.setInt(1, card.getId());
                Statement.setDouble(2, money);
                // 4、执行SQL语句
                int result = Statement.executeUpdate();  // result：表中受影响的行数
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return true;
        } else {
//            System.out.println("余额不足，取钱失败");
            return false;
        }
    }

    public static void putMoneyIn1(Card card, double money) {//存钱基本操作
        String sql = "UPDATE card SET balance=balance+? where id=? ";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setDouble(1, money);
            Statement.setInt(2, card.getId());
            int result = Statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static boolean putMoneyIn(Card card, double money) {//存钱+记录
        putMoneyIn1(card, money);
//        System.out.println("存钱成功");
        String sql = "INSERT INTO record (id_to,amount) VALUES (?,?)";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setInt(1, card.getId());
            Statement.setDouble(2, money);
            int result = Statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }

    public static double getRate(String currency) {//获得汇率
        String sql = "SELECT rate FROM exchange_rate where currency=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setString(1, currency);
            ResultSet set = Statement.executeQuery();
            if (set.next()) {
                return set.getDouble("rate");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0.0;
    }

    public static List<TRecord> getRecords(Card card) {//查询转账记录
        List<TRecord> records = new ArrayList<>();
        String sql = "select id_from, id_to, currency, amount, timestp from record r left outer join card c on id_from = id where (id_from = ? or id_to = ?) order by timestp desc";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setInt(1, card.getId());
            Statement.setInt(2, card.getId());
            ResultSet set = Statement.executeQuery();
            while (set.next()) {
                TRecord rec = new TRecord();
                rec.setIdFrom(set.getInt("id_from"));
                rec.setIdTo(set.getInt("id_to"));
                rec.setCurrency(set.getString("currency"));
                rec.setAmount(set.getDouble("amount"));
                rec.setTimestp(set.getTimestamp("timestp"));
                records.add(rec);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return records;
    }

    public static boolean transferMoney(Card card, int id, double money) {//转账
        if (card.getBalance() >= money) {
            Card c = getCardById(id);
            if (c == null) {
                return false;
            }
            putMoneyOut1(card, money);
            putMoneyIn1(getCardById(id), money * UserDao.getRate(card.getCurrency()) / UserDao.getRate(getCardById(id).getCurrency()));
            String sql = "INSERT INTO record (id_from,id_to,amount) VALUES (?,?,?)";
            try {
                PreparedStatement Statement = conn.prepareStatement(sql);
                Statement.setInt(1, card.getId());
                Statement.setInt(2, id);
                Statement.setDouble(3, money);
                // 4、执行SQL语句
                int result = Statement.executeUpdate();
                if (result > 0) {
//                    System.out.println("转账成功");
                    return true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }  //            System.out.println("余额不足,转账失败");

        return false;
    }

    //更新信息
    public static boolean updatePasswd(Account account, String password) {//修改密码
        String sql = "update account set passwd=? where username=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setString(1, password);
            Statement.setString(2, account.getUsername());
            int result = Statement.executeUpdate();
            if (result > 0) {
//                System.out.println("修改成功");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean updateIdnumber(Account account, String idnumber) {//修改身份证号
        String sql = "update account set idnumber=? where username=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setString(1, idnumber);
            Statement.setString(2, account.getUsername());
            int result = Statement.executeUpdate();
            if (result > 0) {
//                System.out.println("修改成功");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean updateName(Account account, String name) {//修改姓名
        String sql = "update account set name=? where username=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setString(1, name);
            Statement.setString(2, account.getUsername());
            int result = Statement.executeUpdate();
            if (result > 0) {
//                System.out.println("修改成功");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean updateHometown(Account account, String hometown) {//修改籍贯
        String sql = "update account set hometown=? where username=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setString(1, hometown);
            Statement.setString(2, account.getUsername());
            int result = Statement.executeUpdate();
            if (result > 0) {
//                System.out.println("修改成功");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean updatePhone(Account account, String phone) {//修改电话
        String sql = "update account set phone=? where username=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setString(1, phone);
            Statement.setString(2, account.getUsername());
            int result = Statement.executeUpdate();
            if (result > 0) {
//                System.out.println("修改成功");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean updateBalance(Card card, double bl) {//修改电话
        String sql = "update card set balance=? where id=?";
        try {
            PreparedStatement Statement = conn.prepareStatement(sql);
            Statement.setDouble(1, bl);
            Statement.setInt(2, card.getId());
            int result = Statement.executeUpdate();
            if (result > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

}
